package com.lexue.articlesync.util;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库导出工具类
 * 用于导出SQLite数据库的表结构和数据为SQL文件
 */
@Component
public class DatabaseExporter {

    @Autowired
    private DataSource dataSource;

    /**
     * 导出所有表的结构和数据
     */
    public void exportDatabase(String outputPath) throws SQLException, IOException {
        try (Connection conn = dataSource.getConnection();
             FileWriter writer = new FileWriter(outputPath)) {
            
            // 写入文件头部信息
            writeHeader(writer);
            
            // 获取所有表名
            List<String> tableNames = getAllTableNames(conn);
            
            // 导出每个表的结构和数据
            for (String tableName : tableNames) {
                exportTable(conn, writer, tableName);
            }
            
            // 写入文件尾部信息
            writeFooter(writer);
            
            System.out.println("数据库导出完成: " + outputPath);
            System.out.println("导出表数量: " + tableNames.size());
        }
    }

    /**
     * 获取所有表名
     */
    private List<String> getAllTableNames(Connection conn) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        
        DatabaseMetaData metaData = conn.getMetaData();
        try (ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                // 过滤系统表，只导出业务表
                if (!isSystemTable(tableName)) {
                    tableNames.add(tableName);
                }
            }
        }
        
        return tableNames;
    }
    
    /**
     * 判断是否为系统表
     */
    private boolean isSystemTable(String tableName) {
        String upperTableName = tableName.toUpperCase();
        
        // SQLite系统表和索引
        String[] systemPrefixes = {
            "SQLITE_", "ANDROID_", "SYS_"
        };
        
        for (String prefix : systemPrefixes) {
            if (upperTableName.startsWith(prefix)) {
                return true;
            }
        }
        
        // SQLite特殊系统表
        if (upperTableName.equals("SQLITE_SEQUENCE") || 
            upperTableName.equals("SQLITE_MASTER") ||
            upperTableName.equals("SQLITE_TEMP_MASTER")) {
            return true;
        }
        
        // 业务表名列表（只导出这些表）
        String[] businessTables = {
            "ARTICLES", "PLATFORMS", "PLATFORM_AUTH", "PUBLISH_CONFIGS", "VIDEOS"
        };
        
        for (String businessTable : businessTables) {
            if (upperTableName.equals(businessTable)) {
                return false; // 业务表，不是系统表
            }
        }
        
        // 其他表都视为系统表
        return true;
    }

    /**
     * 导出单个表的结构和数据
     */
    private void exportTable(Connection conn, FileWriter writer, String tableName) throws SQLException, IOException {
        writer.write("\n-- ========================================\n");
        writer.write("-- 表: " + tableName + "\n");
        writer.write("-- ========================================\n\n");
        
        // 导出表结构
        exportTableStructure(conn, writer, tableName);
        
        // 导出表数据
        exportTableData(conn, writer, tableName);
    }

    /**
     * 导出表结构
     */
    private void exportTableStructure(Connection conn, FileWriter writer, String tableName) throws SQLException, IOException {
        writer.write("-- 表结构: " + tableName + "\n");
        
        // SQLite不支持SHOW CREATE TABLE，直接使用备用方案
        exportTableStructureAlternative(conn, writer, tableName);
    }

    /**
     * 备用表结构导出方案
     */
    private void exportTableStructureAlternative(Connection conn, FileWriter writer, String tableName) throws SQLException, IOException {
        DatabaseMetaData metaData = conn.getMetaData();
        
        writer.write("DROP TABLE IF EXISTS " + tableName + " CASCADE;\n");
        writer.write("CREATE TABLE " + tableName + " (\n");
        
        List<String> columns = new ArrayList<>();
        
        // 获取列信息
        try (ResultSet columns_rs = metaData.getColumns(null, null, tableName, null)) {
            while (columns_rs.next()) {
                String columnName = columns_rs.getString("COLUMN_NAME");
                String dataType = columns_rs.getString("TYPE_NAME");
                int columnSize = columns_rs.getInt("COLUMN_SIZE");
                String nullable = columns_rs.getString("IS_NULLABLE");
                String autoIncrement = columns_rs.getString("IS_AUTOINCREMENT");
                
                StringBuilder columnDef = new StringBuilder();
                columnDef.append("    ").append(columnName).append(" ").append(dataType);
                
                if (columnSize > 0 && !dataType.equalsIgnoreCase("TIMESTAMP") && !dataType.equalsIgnoreCase("BOOLEAN")) {
                    columnDef.append("(").append(columnSize).append(")");
                }
                
                if ("YES".equalsIgnoreCase(autoIncrement)) {
                    columnDef.append(" AUTOINCREMENT");
                }
                
                if ("NO".equalsIgnoreCase(nullable)) {
                    columnDef.append(" NOT NULL");
                }
                
                columns.add(columnDef.toString());
            }
        }
        
        // 写入列定义
        for (int i = 0; i < columns.size(); i++) {
            writer.write(columns.get(i));
            if (i < columns.size() - 1) {
                writer.write(",");
            }
            writer.write("\n");
        }
        
        // 获取主键信息
        try (ResultSet pk_rs = metaData.getPrimaryKeys(null, null, tableName)) {
            List<String> primaryKeys = new ArrayList<>();
            while (pk_rs.next()) {
                primaryKeys.add(pk_rs.getString("COLUMN_NAME"));
            }
            
            if (!primaryKeys.isEmpty()) {
                writer.write(",    PRIMARY KEY (");
                writer.write(String.join(", ", primaryKeys));
                writer.write(")\n");
            }
        }
        
        writer.write(");\n\n");
    }

    /**
     * 导出表数据
     */
    private void exportTableData(Connection conn, FileWriter writer, String tableName) throws SQLException, IOException {
        writer.write("-- 表数据: " + tableName + "\n");
        
        String sql = "SELECT * FROM " + tableName;
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            // 构建列名
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
            }
            
            // 导出数据
            int rowCount = 0;
            while (rs.next()) {
                if (rowCount == 0) {
                    writer.write("INSERT INTO " + tableName + " (");
                    writer.write(String.join(", ", columnNames));
                    writer.write(") VALUES\n");
                }
                
                writer.write(rowCount == 0 ? "    (" : ",\n    (");
                
                for (int i = 1; i <= columnCount; i++) {
                    Object value = rs.getObject(i);
                    if (value == null) {
                        writer.write("NULL");
                    } else if (value instanceof String || value instanceof Timestamp) {
                        writer.write("'");
                        writer.write(value.toString().replace("'", "''"));
                        writer.write("'");
                    } else {
                        writer.write(value.toString());
                    }
                    
                    if (i < columnCount) {
                        writer.write(", ");
                    }
                }
                
                writer.write(")");
                rowCount++;
            }
            
            if (rowCount > 0) {
                writer.write(";\n");
            }
            
            writer.write("-- " + tableName + " 表数据记录数: " + rowCount + "\n\n");
        }
    }

    /**
     * 写入文件头部信息
     */
    private void writeHeader(FileWriter writer) throws IOException {
        writer.write("-- ========================================\n");
        writer.write("-- ArticleSync 数据库备份文件\n");
        writer.write("-- 导出时间: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) + "\n");
        writer.write("-- 数据库类型: SQLite Database\n");
        writer.write("-- ========================================\n\n");
        
        writer.write("-- SQLite设置\n");
        writer.write("PRAGMA foreign_keys = OFF;\n\n");
    }

    /**
     * 写入文件尾部信息
     */
    private void writeFooter(FileWriter writer) throws IOException {
        writer.write("\n-- ========================================\n");
        writer.write("-- 导出完成\n");
        writer.write("-- ========================================\n");
        writer.write("PRAGMA foreign_keys = ON;\n");
    }
}